package com.htht.smartschool.service.teacher;

import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.htht.smartschool.common.service.BaseService;
import com.htht.smartschool.model.attendance.AttendanceStatisticsData;
import com.htht.smartschool.model.attendance.TAttendanceStatistics;
import com.htht.smartschool.model.card.TCard;
import com.htht.smartschool.model.common.TUserStudent;
import com.htht.smartschool.model.course.TeacherTime;
import com.htht.smartschool.model.exam.ExamResultAll;
import com.htht.smartschool.model.exam.ExamResultData;
import com.htht.smartschool.model.exam.TExaminationresult;
import com.htht.smartschool.model.exam.TExampage;
import com.htht.smartschool.model.message.TMessage;
import com.htht.smartschool.model.teacher.TClass;
import com.htht.smartschool.model.teacher.TTeacher;
import com.htht.smartschool.model.user.TUser;
import com.htht.smartschool.service.card.CardService;
import com.htht.smartschool.service.user.UserService;
import com.htht.smartschool.util.cmpp.CmppUtil;
import com.htht.smartschool.util.message.MessageUtil;
import com.htht.smartschool.util.system.ConfigUtil;
import com.htht.smartschool.util.system.DateUtil;
import com.htht.smartschool.util.system.EncryptUtil;
import com.htht.smartschool.util.system.GsonUtils;
import com.htht.smartschool.util.system.ResultSetToExcel;

public class TeacherService extends BaseService{
	public static void main(String[] args) {
		TTeacher teacher = new TTeacher();
		teacher.setCardid("1102271990010102450'");
		System.out.println(teacher.getCardid().replaceAll("'",""));
	}
	public boolean addTeacher(TTeacher tt) {
		UserService userService = new UserService();
		TTeacher teacher = getTeacher(tt.getCellphone(),tt.getTeachercode());
		//将含有'的字段 用空格去掉
		if(tt.getCardid() != null){
			if(tt.getCardid().contains("'")){
				tt.getCardid().replaceAll("'",""); 
				tt.setCardid(tt.getCardid().replaceAll("'",""));
			}
		}
		if(tt.getTeacherno() != null){
			if(tt.getTeacherno().contains("'")){
				tt.getTeacherno().replaceAll("'",""); 
				tt.setTeacherno(tt.getTeacherno().replaceAll("'",""));
			}
		}
		if(tt.getTeachercode() != null){
			if(tt.getTeachercode().contains("'")){
				tt.getTeachercode().replaceAll("'",""); 
				tt.setTeachercode(tt.getTeachercode().replaceAll("'",""));
			}
		}
		if(teacher != null){
			return false;
		}else{
			tt.setDel("1");
			tt.setTeacherno(tt.getTeachercode());
			getDao().create(tt);
			TUser tuser = new TUser();
			tuser.setUsercode(tt.getCellphone());
			tuser = userService.getUserDetail(tuser.getUsercode());
			if(tt.getDepartmentcode() != null && tt.getDepartmentcode().length() > 0){
				String departmentsql = "INSERT INTO t_teacher_department (id,teachercode,departmentcode) VALUES ( NULL,:teachercode,:departmentcode);";
				getDao().executeSQL(departmentsql,tt);
			}
			
			if (tuser == null || tuser.getUsertype() == 1) {
				TUser u = new TUser();
				u.setUsercode(tt.getCellphone());
				u.setHeadimg(ConfigUtil.getProperty("default","headimg"));
				u.setPassword(EncryptUtil.encryptMd5Password2(tt.getCellphone()));
				u.setUserkey(EncryptUtil.encryptMd5Password2(tt.getCellphone()+"2"));
				u.setHeadimg(ConfigUtil.getProperty("default","headimg"));
				u.setUsertype(2);
				u.setAlias(tt.getName());
				u.setIslock(true);
				u.setLockdate((long) 0);
				u.setLocktype(1);
				u.setCellphone(tt.getCellphone());
				u.setAddress(tt.getBirpacle());
				u.setRegtime((new Date().getTime())/1000);
				u.setSchoolcode(tt.getSchoolcode());
				UserService us = new UserService();
				String mesid = us.regPushUser(u.getUsercode()+u.getUsertype(),u.getUsertype());
				u.setMessageid(mesid);
				getDao().create(u);
			}
			return true;
		}
	}

	public TTeacher updateTeacher(TTeacher tteacher) {
		TTeacher tt = getTeacherDetail(tteacher.getTeachercode());
		if (tt != null) {
			for (Field f : tteacher.getClass().getDeclaredFields()) {
			    f.setAccessible(true);
			    try {
					if (f.get(tteacher) != null && !f.getName().equalsIgnoreCase("serialVersionUID")) {
					   f.set(tt, f.get(tteacher));
					}
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
			if(tteacher.getDepartmentcode() != null && tteacher.getDepartmentcode().length() > 0){
				String sql = "UPDATE t_teacher_department SET departmentcode=:departmentcode WHERE teachercode=:teachercode";
				getDao().executeSQL(sql,tteacher);
			}
			tteacher = (TTeacher) getDao().update(tt);
			return tteacher;
		}else{
			return null;
		}
	}
	/**
	 * 判断数据库是否存在
	 * 
	 * @param usercode
	 * @return
	 */
	public TTeacher getTeacherDetail(String teachercode) {
		TTeacher tteacher = null;
		try {
			String hql = String.format("from TTeacher where %1$s='%2$s'", "teachercode", teachercode);
			tteacher = (TTeacher) getDao().queryFirstByHQL(hql);
			if (tteacher == null) {
				return tteacher;
			} 
		} catch (Exception e) {
			e.printStackTrace();
		}
		return tteacher;
	}
	public boolean deleteTeacher(TTeacher tt) {
		getDao().delete(tt);
		if(tt.getDepartmentcode() != null && tt.getDepartmentcode().length() > 0){
			String sql = "DELETE FROM t_teacher_department WHERE departmentcode=:departmentcode WHERE teachercode=:teachercode";
			getDao().executeSQL(sql,tt);
		}
		return false;
	}

	public TTeacher getTeacher(String cellphone,String teachercode) {
		String hql = "from TTeacher where del = '1' and (cellphone = :cellphone or teachercode = :teachercode )";
		TTeacher teacher =new TTeacher();
		teacher.setCellphone(cellphone);
		teacher.setTeachercode(teachercode);
		TTeacher tteacher = (TTeacher) getDao().queryFirstByHQL(hql,teacher);
		return tteacher;
	}
	public TTeacher getTeacher(String cellphone) {
		String hql = "from TTeacher where del = '1' and cellphone = :cellphone";
		TTeacher teacher = new TTeacher();
		teacher.setCellphone(cellphone);
		TTeacher tteacher = (TTeacher) getDao().queryFirstByHQL(hql,teacher);
		String classhsql = "from TClass where teachercode = :teachercode";
		TClass tc = (TClass) getDao().queryFirstByHQL(classhsql,tteacher);
		if(tc != null && tc.getClasscode() != null){
			tteacher.setGradeclass(tc.getGrade()+"年级"+tc.getClass_()+"班");
			tteacher.setClasscode(tc.getClasscode());
		}
		return tteacher;
	}
	/***
	 * 获取考勤统计信息
	 * @param tteacher
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<AttendanceStatisticsData> getClassAttendanceByTeacherCode(TTeacher tteacher) {
		List<TClass> list = getAllClasses(tteacher.getTeachercode());
		List<AttendanceStatisticsData> result = new ArrayList<AttendanceStatisticsData>();
		for (int i = 0; i < list.size(); i++) {
			AttendanceStatisticsData attendancestatisticsdata = new AttendanceStatisticsData();
			TClass tc = list.get(i);
			attendancestatisticsdata.setCalss(tc.getGrade()+"年级"+tc.getClass_()+"班");
			attendancestatisticsdata.setCalssteacher(tc.getTeachercode());
			String sql = "SELECT tas FROM TAttendanceStatistics AS tas ,TTeacher AS tt WHERE tas.time <= :endtime and tas.time >= :starttime"
					+ " and tt.teachercode = tas.teachercode and tt.teachercode = :teachercode and tas.classcode = :classcode";
			List<TAttendanceStatistics> attendances =  getDao().queryByHQL(sql,tteacher);
			attendancestatisticsdata.setAttendances(attendances);
			result.add(attendancestatisticsdata);
		}
		return result;
	}
	/***
	 * 获取教师排课表
	 * @param teachercode
	 * @return
	 */
	public List<TeacherTime> getTeacherTimeByTeacherCode(String teachercode) {
		String sql = "SELECT DISTINCT tc.id,tcl.grade,tcl.class,tcs.coursecontent,tc.courseno,tc.coursetime,tc.weekno,tc.semester "
				+ " FROM t_course tc,t_courseorder tco,t_coursesubject tcs,t_class tcl "
				+" WHERE  tco.teachercode =:teachercode AND tc.coursecode = tco.coursecode AND tcs.subjectcode = tc.subjectcode AND tcl.classcode = tc.classcode";
		Date now = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
		String time = DateUtil.getFormatDate(now,sdf);
		@SuppressWarnings("unchecked")
		TTeacher tteacher =  new TTeacher();
		tteacher.setTeachercode(teachercode);
		List<Object[]> list = getDao().queryBySQL(sql,tteacher);
		List<TeacherTime> result =  new ArrayList<TeacherTime>();
		for (int i = 0; i < list.size(); i++) {
			Object[] obj = list.get(i);
			TeacherTime tt = new TeacherTime();
			tt.setGradeclass(String.valueOf(obj[1])+"年级"+String.valueOf(obj[2])+"班");
			tt.setCoursecontent(String.valueOf(obj[3]));
			tt.setCourseno(String.valueOf(obj[4]));
			tt.setCoursetime(String.valueOf(obj[5]));
			tt.setWeekno(String.valueOf(obj[6]));
			String str = "";
			if("1".equalsIgnoreCase(String.valueOf(obj[7]))){
				str = "上学期";
			}else{
				str = "下学期";
			}
			tt.setSemester(time+"年级"+str);
			result.add(tt);
		}
		return result;
	}
	/***
	 * 按分页获取教师列表,查询教师,参数部门,科室,姓名进行筛选
	 * @param department1
	 * @param department1
	 * @param name
	 * @param currentpage
	 * @param pagesize
	 * @return map
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map getAllTeachers(TTeacher tteacher) {
		String dep1Sql = "";
		String dep2Sql = "";
		String nameSql = "";
		String name = "";
		String dep1 = tteacher.getDepartment1();
		String dep2 = tteacher.getDepartment2();
		if(!tteacher.getName().equalsIgnoreCase("")){
			name = tteacher.getName().trim();
		}
		if(tteacher.getDel() == null){
			tteacher.setDel("1");
		}
		if(dep1 != null && !dep1.equals("部门")){
			if(dep1.trim() != ""){
				dep1Sql = " and tt.department1 like :department1";
				tteacher.setDepartment1("%"+dep1+"%");
			}
		}
		if(dep2 != null && !dep2.equals("科室")){
			if(dep2.trim() != ""){
				dep2Sql = " or tt.department2 like :department2";
				tteacher.setDepartment2("%"+dep2+"%"); 
			}
		}
		if(!name.equalsIgnoreCase("")){
			if(name.trim() != ""){
				nameSql = " and tt.name like :name";
				tteacher.setName("%"+name+"%");
				
			}
		}
		String sql = "select DISTINCT tt.*,ttd.departmentcode from t_teacher tt LEFT JOIN t_teacher_department ttd ON "
				+ "tt.teachercode = ttd.teachercode  where tt.del = :del" + dep1Sql + dep2Sql + nameSql;
		if(tteacher.getSchoolcode() != null && tteacher.getSchoolcode().length() > 0){
			sql += " and tt.schoolcode = :schoolcode";
		}	  
		String sql2 = "select count(*) from("+sql+") a";
		int count = getDao().getCountBySqlPage(sql2,tteacher);
		System.out.println(GsonUtils.getInstance().toJson(tteacher));
		List<Object[]> list = null;
		if(tteacher.getCurrentpage() == null || tteacher.getCurrentpage() == 0){
			list = getDao().queryBySQL(sql,tteacher);
		}else{
			list = getDao().queryBySqlPage(sql,tteacher, tteacher.getCurrentpage(), tteacher.getPagesize());
		}
//		ResultSetToExcel.rs = list;
//		ResultSetToExcel.coloumItem = "id,教师编号,部门,科室,姓名,性别,民族,出生年月,籍贯,身份证号,政治面貌,"
//				+ "文化程度,从事工作,专业技术职务,发证单位,参加工作时间,来校工作时间,毕业院校及所学专业,角色,教师编码,学校编码,备注,电话,手机,删除状态,部门编码";
		ResultSetToExcel rse = null;
		if(tteacher.getIsdownload() != null && tteacher.getIsdownload().equalsIgnoreCase("true")){
			rse = new ResultSetToExcel();
			rse.setColoumItem("姓名,教师编号,部门,科室,部门编码,性别,民族,出生年月,籍贯,身份证号,政治面貌,文化程度,从事工作,专业技术职务,发证单位,参加工作时间,来校工作时间,毕业院校及所学专业,角色,教师编码,学校编码,电话,手机,删除状态,备注,部门编码");
			rse.setRs(list);
			rse.writeExcel();
		}
		List<TTeacher> result = new ArrayList<TTeacher>();
		
		for(int i = 0; i < list.size(); i++){
			TTeacher tt = new TTeacher();
			Object[] obj = list.get(i);
			
			tt.setTeacherno(String.valueOf(obj[1]));
			tt.setDepartment1(String.valueOf(obj[2]));
			tt.setDepartment2(String.valueOf(obj[3]));
			tt.setName(String.valueOf(obj[4]));
			tt.setSex(String.valueOf(obj[5]));
			tt.setNation(String.valueOf(obj[6]));
			tt.setBirdate(Long.parseLong(String.valueOf(obj[7])));
			tt.setBirpacle(String.valueOf(obj[8]));
			tt.setCardid(String.valueOf(obj[9]));
			tt.setPolitical(String.valueOf(obj[10]));
			tt.setDegree(String.valueOf(obj[11]));
			tt.setWork(String.valueOf(obj[12]));
			tt.setTjob(String.valueOf(obj[13]));
			tt.setIssueorg(String.valueOf(obj[14]));
			String workdate = String.valueOf(obj[15]);
			tt.setWorkdate(Long.parseLong(workdate));
			tt.setInschooldate(Long.parseLong(String.valueOf(obj[16])));
			tt.setGraduation(String.valueOf(obj[17]));
			tt.setRole(String.valueOf(obj[18]));
			tt.setTeachercode(String.valueOf(obj[19]));
			tt.setSchoolcode(String.valueOf(obj[20]));
			tt.setPhone(String.valueOf(obj[21]));
			tt.setCellphone(String.valueOf(obj[22]));
			tt.setDel(String.valueOf(obj[23]));
			tt.setRemark(String.valueOf(obj[24]));
			tt.setDepartmentcode(String.valueOf(obj[25]));
			result.add(tt);
		}
		Map res = new HashMap();
		res.put("total", count);
		if(rse != null){
			rse.setRs(null);
			res.put("remark", rse);
		}
		res.put("data", result);
		return res;
	}
	/***
	 * 批量删除教师
	 * @param teachercodes
	 * @return flag
	 */
	@SuppressWarnings("rawtypes")
	public Boolean batchDeleteTeachersByCodes(TTeacher tteacher) {
		List codes = tteacher.getTeachercodes();
		boolean flag = true;
		String values = "";
		for(int i = 0; i < codes.size(); i++){
			values += "'" + codes.get(i) + "',";
		}
		values = values.substring(0,values.length()-1);
		String sql = "update t_teacher set del = '0' where teachercode in (" + values + ")";
		int res = getDao().executeSQL(sql);
		if(res == -1){
			flag = false;
		}
		return flag;
	}
	/***
	 * 解锁教师
	 * @param teachercodes
	 * @return flag
	 */
	public Boolean unLockTeachersByCodes(TTeacher tteacher) {
		String teachercode = tteacher.getTeachercode();
		boolean flag = true;
		String sql = "update t_teacher set del = '1' where teachercode = :teachercode";
		int res = getDao().executeSQL(sql,tteacher);
		if(res == -1){
			flag = false;
		}
		return flag;
	}
	@SuppressWarnings("unchecked")
	public List<ExamResultData> getExamsByClassCode(String classcode) {
		String sql = "SELECT ter.id,ter.classcode,ter.examcode,ter.studentcode,ter.result,tcs.coursecontent,ts.name "
				+"FROM t_examinationresult ter,t_exam te,t_coursesubject tcs,t_student ts "
				+"WHERE ter.classcode = :classcode AND ter.examcode = te.examcode AND te.subjectcode = tcs.subjectcode AND ter.studentcode = ts.studentcode "
				+"ORDER BY ter.examcode ASC";
		String coursecontentsql = "SELECT tcs.coursecontent,te.starttime,te.endtime,te.resulttime,te.remark FROM t_examinationresult ter,t_exam te,t_coursesubject tcs,t_student ts "
				+"WHERE ter.classcode = :classcode AND ter.examcode = te.examcode AND te.subjectcode = tcs.subjectcode AND ter.studentcode = ts.studentcode "
				+"GROUP BY tcs.coursecontent ORDER BY ter.examcode ASC";
		TTeacher tteacher = new TTeacher();
		tteacher.setClasscode(classcode);
		List<Object[]> list = getDao().queryBySQL(sql,tteacher);
		List<Object[]> coursecontents = getDao().queryBySQL(coursecontentsql,tteacher);
		List<ExamResultData> result = new ArrayList<ExamResultData>();
		for (Object[] coursecontent : coursecontents) {
			String cc = String.valueOf(coursecontent[0]);
			ExamResultData erd = new ExamResultData();
			List<TExaminationresult> ters = new ArrayList<TExaminationresult>();
			for (Object[] obj:list) {
				if(cc.equalsIgnoreCase(String.valueOf(obj[5]))){
					TExaminationresult ter = new TExaminationresult();
					ter.setId(Long.getLong(String.valueOf(obj[0])));
					ter.setClasscode(String.valueOf(obj[1]));
					ter.setExamcode(String.valueOf(obj[2]));
					ter.setStudentcode(String.valueOf(obj[3]));
					ter.setResult(String.valueOf(obj[4]));
					ter.setName(String.valueOf(obj[6]));
					ters.add(ter);
				}
			}
			erd.setStarttime(Long.getLong(String.valueOf(coursecontent[1])));
			erd.setEndtime(Long.getLong(String.valueOf(coursecontent[2])));
			erd.setResulttime(Long.getLong(String.valueOf(coursecontent[3])));
			erd.setRemark(String.valueOf(coursecontent[4]));
			erd.setCoursecontent(cc);
			erd.setResult(ters);
			result.add(erd);
		}
		return result;
	}

	public String sendMessage(TMessage tme) {
		String content = tme.getContent();
		System.out.println(content.length());
		if(content.length() >200){
			this.setMsg("文字超过限制!最大文字个数为200");
			return null;
		}
		TMessage me = MessageUtil.getMessageBody(tme);
		String messagejson = "message&"+GsonUtils.getInstance().toJson(me);
		if(me.getChannel() == null){
			me.setChannel(0);
		}
		String result = "";
		if(tme.getCmpptype() != null){
			if(tme.getCmpptype().equalsIgnoreCase("1") ||  tme.getCmpptype().equalsIgnoreCase("2")){
				result = String.valueOf(CmppUtil.sendToCmpp(content, me.getCellphones()));
			}
		}
		result = MessageUtil.sendToClient(me.getContent(),messagejson, me.getTo(),me.getSendType(),me.getChannel(),me.getFrom());
		String[] results = result.split("#");
		String mesid_user = "";
		String mesid_teacher = "";
		for (String str : results) {
			String[] apres = str.split(",");
			if(apres[0].equalsIgnoreCase(ConfigUtil.getProperty("message","teacherid"))){
				mesid_teacher = apres[1];
			}else{
				mesid_user = apres[1];
			}
		}
		if(me.getType().equalsIgnoreCase("1")||me.getType().equalsIgnoreCase("2")||me.getType().equalsIgnoreCase("4")||me.getType().equalsIgnoreCase("6")||me.getType().equalsIgnoreCase("7")){
			if(mesid_user.length() == 0){
				mesid_user = "0";
			}
			if(mesid_teacher.length() == 0){
				mesid_teacher = "0";
			}
			String sql = "INSERT INTO t_message (id,type,sendcode,receivecode,content,idatetime,mesid_user,mesid_teacher,schoolcode) VALUES "
					+ "( NULL,:type,:sendcode,:receivecode,:content,:idatetime,'"+mesid_user+"','"+mesid_teacher+"','"+tme.getSchoolcode()+"')";
			getDao().executeSQL(sql,me);
		}
		return result;
	}
	public String senIntoSchoolMessge(TCard card) {
		String result = null;
		try {
			String content = "";
			if(card.getGpsstatus().equalsIgnoreCase("OFF")){
				content = "您的孩子已经进入学校！";
			}else{
				content = "您的孩子已经离开学校！";
			}
			CardService cs = new CardService();
			TUserStudent tus = cs.getUSIByImei(card.getImei());
			if(tus != null){
				TMessage tme = new TMessage();
				tme.setContent(URLEncoder.encode(content, "utf-8"));
				tme.setFrom(0L);
				tme.setTo(tus.getTo());
				tme.setType("3");
				result = sendMessage(tme);
			}
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		return result;
	}

	@SuppressWarnings("unchecked")
	public List<TClass> getAllClasses(String teachercode) {
		String sql = "SELECT DISTINCT tc.classcode,tco.teachercode,tcl.grade,tcl.class,tcl.type"
				+" FROM t_course tc,t_courseorder tco,t_class tcl WHERE tco.teachercode = :teachercode AND tc.classcode = tcl.classcode  AND tc.coursecode = tco.coursecode";
		String classsql = "SELECT DISTINCT tcl.classcode,tcl.teachercode,tcl.grade,tcl.class,tcl.type FROM t_class tcl WHERE tcl.teachercode = :teachercode";
		TTeacher tteacher = new TTeacher();
		tteacher.setTeachercode(teachercode);
		List<Object[]> list = getDao().queryBySQL(sql,tteacher);
		List<Object[]> classlist = getDao().queryBySQL(classsql,tteacher);
		List<TClass> result = new ArrayList<TClass>();
		TClass tcl = new TClass();
		for (Object[] obj : classlist) {
			tcl = new TClass();
			tcl.setClasscode(String.valueOf(obj[0]));
			tcl.setTeachercode(String.valueOf(obj[1]));
			tcl.setGrade(Integer.parseInt(String.valueOf(obj[2])));
			tcl.setClass_(Integer.parseInt(String.valueOf(obj[3])));
			tcl.setName(tcl.getGrade()+"年级"+tcl.getClass_()+"班");
			tcl.setSchooltype(String.valueOf(obj[4]));
			result.add(tcl);
		}
		for (Object[] obj : list) {
			TClass tc = new TClass();
			tc.setClasscode(String.valueOf(obj[0]));
			tc.setTeachercode(String.valueOf(obj[1]));
			tc.setGrade(Integer.parseInt(String.valueOf(obj[2])));
			tc.setClass_(Integer.parseInt(String.valueOf(obj[3])));
			tc.setName(tc.getGrade()+"年级"+tc.getClass_()+"班");
			tc.setSchooltype(String.valueOf(obj[4]));
			if(!tc.getName().equalsIgnoreCase(tcl.getName())){
				boolean isOk = false;
				for(TClass tclass : result){
					if(tc.getName().equals(tclass.getName())){
						isOk = true;
					}
				}
				if(!isOk){
					result.add(tc);
				}
			}
		}
		return result;
	}
	@SuppressWarnings("unchecked")
	public List<TMessage> getMessageByMessageId(TMessage tme) {
		String sql = "SELECT * FROM t_message WHERE id >= '1' ";
		String mesid ="";
		mesid = tme.getAppid().equalsIgnoreCase(ConfigUtil.getProperty("message","teacherid")) ? "mesid_teacher" : "mesid_user";
		if(tme.getMesid() != null && tme.getMesid() > 0){
			
			if(tme.getFlag().equalsIgnoreCase("0")){
				sql += " and "+mesid+" < :mesid";
			}else if(tme.getFlag().equalsIgnoreCase("1")){
				sql += " and "+mesid+" > :mesid";
			}
		}
		if(tme.getType() != null){
			sql += " and type = :type";
		}
		if(tme.getAppid() != null){
			sql += " and receivecode like '%"+tme.getAppid()+"%'";
		}
		if(tme.getStarttime() != null && tme.getStarttime()>0){
			sql += " and idatetime >= :starttime";
		}
		if(tme.getEndtime() != null && tme.getEndtime()>0){
			sql += " and idatetime <= :endtime";
		}
		sql += " ORDER BY "+mesid+" desc";
		List<Object[]> list = null;
		if(tme.getCurrentpage()!=null && tme.getPagesize() != null && tme.getCurrentpage()>0 && tme.getPagesize()>0){
			list = getDao().queryBySqlPage(sql,tme, tme.getCurrentpage(), tme.getPagesize());
		}else{
			list = getDao().queryBySQL(sql,tme);
		}
		List<TMessage> result = new ArrayList<TMessage>();
		for (Object[] obj : list) {
			TMessage me = new TMessage();
			me.setSendcode(tme.getSendcode());
			me.setReceivecode(tme.getReceivecode());
			me.setType(String.valueOf(obj[1]));
			me.setContent(String.valueOf(obj[4]));
			me.setIdatetime(Long.valueOf(String.valueOf(obj[5])));
			me.setMesid(Long.valueOf(tme.getAppid().equalsIgnoreCase(ConfigUtil.getProperty("message","teacherid")) ? String.valueOf(obj[7]) : String.valueOf(obj[6])));
			result.add(me);
		}
		return result;
	}
	@SuppressWarnings("unchecked")
	public List<TMessage> getMessage(TMessage tme) {
		String sql = "SELECT tm.content,tm.idatetime,'管理员' AS NAME FROM t_message tm ";
		int type = Integer.parseInt(tme.getType());
		switch (type) {
		case 4:
			sql = "SELECT tm.content,tm.idatetime,CONCAT(tc.grade,'年级',tc.class,'班') FROM t_message tm,t_class tc ";
			break;
		case 5:
			sql = "SELECT tm.content,tm.idatetime,tu.alias AS username FROM t_message tm,t_user tu ";
			break;	
		default:
			break;
		}
		sql += " WHERE tm.type = :type ";		
		if(tme.getReceivecode() != null && tme.getReceivecode().length() >0){
			tme.setReceivecode("%"+tme.getReceivecode()+"%");
			sql += " AND tm.receivecode like :receivecode";
		}
		if(tme.getSendcode() != null && tme.getSendcode().length() >0){
			tme.setReceivecode(tme.getReceivecode().replace(",", "^"));
			sql += " AND tm.receivecode in :sendcode";
		}
		if(tme.getEndtime()!= null && tme.getEndtime()>0){
			sql +=  " AND tm.idatetime <= :endtime";
		}
		if(tme.getStarttime()!= null && tme.getStarttime()>0){
			sql += " AND tm.idatetime >= :starttime";
		}
		if(type == 4){
			sql += "  AND tc.classcode = tm.receivecode ";
		}
		if(tme.getSchoolcode() != null && tme.getSchoolcode().length() > 0){
			sql += " and tm.schoolcode = :schoolcode";
		}
		sql += " ORDER BY tm.idatetime desc";
		List<Object[]> list = getDao().queryBySqlPage(sql,tme, tme.getCurrentpage(), tme.getPagesize());
		int total = getDao().getCountBySqlPage("select count(*) from ("+sql+") tc",tme);
		List<TMessage> result = new ArrayList<TMessage>();
		if(tme.getReceivecode() != null && tme.getReceivecode().length() >0){
			tme.setReceivecode(tme.getReceivecode().replace("#", ","));
		}
		for (Object[] obj : list) {
			TMessage me = new TMessage();
			me.setTotal((long) total);
			me.setSendcode(tme.getSendcode());
			me.setReceivecode(tme.getReceivecode());
			me.setContent(String.valueOf(obj[0]));
			me.setIdatetime(Long.valueOf(String.valueOf(obj[1])));
			me.setName(String.valueOf(obj[2]));
			result.add(me);
		}
		return result;
	}
	@SuppressWarnings("unchecked")
	public List<TUser> getUseridByClasscode(String classcode){
		String sql ="SELECT DISTINCT tu.usercode,tu.alias,tu.messageid "
				+"FROM t_student ts,t_user_student tus,t_user tu "+ 
				"WHERE ts.classcode = :classcode AND ts.del = '1' AND ts.studentcode = tus.studentcode AND tus.usercode = tu.usercode";
		TTeacher tteacher = new TTeacher();
		tteacher.setClasscode(classcode);
		List<Object[]> list = getDao().queryBySQL(sql,tteacher);
		List<TUser> result = new ArrayList<TUser>();
		for (Object[] obj : list) {
			TUser tu = new TUser();
			tu.setUsercode(String.valueOf(obj[0]));
			tu.setAlias(String.valueOf(obj[1]));
			tu.setMessageid(String.valueOf(obj[2]));
			result.add(tu);
		}
		return result;
	}
	@SuppressWarnings("unchecked")
	public String getGradeClassByClasscode(String classcode){
		String result = "";
		String sql ="SELECT DISTINCT tc.grade,tc.class "
				+"FROM t_class tc "+ 
				"WHERE tc.classcode = :classcode";
		TTeacher tteacher = new TTeacher();
		tteacher.setClasscode(classcode);
		List<Object[]> list = getDao().queryBySQL(sql,tteacher);
		for (Object[] obj : list) {
			result = String.valueOf(obj[0])+"年级"+String.valueOf(obj[1])+"班";
		}
		return result;
	}
	@SuppressWarnings("unchecked")
	public List<TExampage> getExamsByPage(String subjectCode,Long startTime,Long endTime,Long currentpage,Long pageSize,String schoolcode){
		List<TExampage> listResult = new ArrayList<TExampage>();
		String sql = "SELECT t1.examcode,t1.subjectcode,t2.coursecontent,t1.starttime,t1.endtime,t1.resulttime,t1.remark FROM t_exam t1,(SELECT subjectcode,coursecontent FROM t_coursesubject) t2 WHERE t1.subjectcode = t2.subjectcode ";
		TExampage texampage = new TExampage();
		if(subjectCode!=null){
			sql += " and t1.subjectcode = :subjectcode ";
			texampage.setSubjectcode(subjectCode);
		}
		if(startTime!=null){
			sql += " and t1.starttime > "+startTime+" and t1.endtime < "+endTime+" ";
		}
		if(schoolcode != null){
			sql += " and t1.schoolcode = '"+schoolcode+"'";
		}
		sql+=  " ORDER BY t1.starttime desc";
		List<Object[]> list = getDao().queryBySqlPage(sql,texampage, currentpage, pageSize);
		int total = getDao().getCountBySqlPage("select count(*) from ("+sql+") t3",texampage);
		for(Object[] obj : list){
			TExampage tExampage = new TExampage();
			tExampage.setExamcode(String.valueOf(obj[0]));
			tExampage.setSubjectcode(String.valueOf(obj[1]));
			tExampage.setCoursecontent(String.valueOf(obj[2]));
			tExampage.setStarttime(Long.valueOf(String.valueOf(obj[3])));
			tExampage.setEndtime(Long.valueOf(String.valueOf(obj[4])));
			tExampage.setResulttime(String.valueOf(obj[5]));
			tExampage.setRemark(String.valueOf(obj[6]));
			tExampage.setTotal((long) total);
			listResult.add(tExampage);
		}
		return listResult;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map getExamsresultAll(ExamResultAll era){
		String sql = "SELECT :examcode examcode,tc.classcode,tc.grade,tc.class,ts.name,ts.studentcode,IFNULL(ter.result,0) result "
				+" FROM t_student ts LEFT JOIN t_class tc ON ts.classcode = tc.classcode "
				+" LEFT JOIN (SELECT * FROM t_examinationresult WHERE examcode = :examcode) ter ON ter.studentcode = ts.studentcode"
				+" where ts.studentcode > 0 "; 
		if(era.getClasscode() !=null && era.getClasscode().length() > 0){
			sql += " and tc.classcode = :classcode ";
		}
		if(era.getStudentcode() !=null && era.getStudentcode().length() > 0){
			sql += " and ts.studentcode = :studentcode";
		}
		if(era.getSchoolcode() != null && era.getSchoolcode().length() > 0){
			sql += " and ts.schoolid = :schoolcode";
		}
		sql += " ORDER BY ter.result+0 desc";
		List<Object[]> list = getDao().queryBySQL(sql,era);
		ResultSetToExcel rse = null;
		if(era.getIsdownload() != null && era.getIsdownload().equalsIgnoreCase("true")){
			rse = new ResultSetToExcel();
			rse.setColoumItem( "考试编码,班级编码,年级,班,姓名,学生编码,分数");
			rse.setRs(list);
			rse.writeExcel();
		}
		List<ExamResultAll> listResult = new ArrayList<ExamResultAll>();
		for (Object[] obj : list) {
			ExamResultAll examResultAll = new ExamResultAll();
			examResultAll.setStudentcode(String.valueOf(obj[5]));
			examResultAll.setName(String.valueOf(obj[4]));
			examResultAll.setResult(String.valueOf(obj[6]));
			examResultAll.setGrade(String.valueOf(obj[2]));
			examResultAll.setClasses(String.valueOf(obj[3]));
			examResultAll.setClasscode(String.valueOf(obj[1]));
			listResult.add(examResultAll);
		}
		Map map = new HashMap();
		map.put("list", listResult);
		if(rse != null){
			rse.setRs(null);
			map.put("remark", rse);
		}
		return map;
	}
	@SuppressWarnings("unchecked")
	public ResultSetToExcel getExamsresultAll_Model(ExamResultAll era){
		String sql = "SELECT :examcode examcode,tc.classcode,tc.grade,tc.class,ts.name,ts.studentcode,IFNULL(ter.result,'') result "
				+" FROM t_student ts LEFT JOIN t_class tc ON ts.classcode = tc.classcode "
				+" LEFT JOIN (SELECT * FROM t_examinationresult WHERE examcode = :examcode) ter ON ter.studentcode = ts.studentcode"
				+" where ts.studentcode > 0 "; 
		if(era.getClasscode() !=null && era.getClasscode().length() > 0){
			sql += " and tc.classcode = :classcode ";
		}
		if(era.getStudentcode() !=null && era.getStudentcode().length() > 0){
			sql += " and ts.studentcode = :studentcode";
		}
		sql += " ORDER BY ter.result+0 desc";
		List<Object[]> list = getDao().queryBySQL(sql,era);
		ResultSetToExcel rse = null;
		if(era.getIsdownload() != null && era.getIsdownload().equalsIgnoreCase("true")){
			rse = new ResultSetToExcel();
			rse.setColoumItem("examcode,classcode,grade,class,name,studentcode,result");
			rse.setRs(list);
			rse.writeExcel();
		}
		return rse;
	}
	public boolean addExam(ExamResultAll examResultAll){
		boolean flag = false;
		String sql = "insert into t_exam(examcode,subjectcode,starttime,endtime,resulttime,remark,usercode,usertype,schoolcode) values('"+examResultAll.getTimestr()+"','"+examResultAll.getSubjectcode()+"','"+examResultAll.getStarttime()+"','"+examResultAll.getEndtime()+"','"+examResultAll.getResulttime()+"','"+examResultAll.getRemark()+"','"+examResultAll.getUsercode()+"','"+examResultAll.getUsertype()+"','"+examResultAll.getSchoolcode()+"')";
		int a =  getDao().executeSQL(sql);
		if( a > 0){
			flag  = true;
		}
		return flag;
	}
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<ExamResultAll> examJudge(String examCode){
		String sql = "select * from t_exam where examcode = :examcode";
		ExamResultAll examResultAll = new ExamResultAll();
		examResultAll.setExamcode(examCode);
		List list = getDao().queryBySQL(sql,examResultAll);
		return list;
	}
	public int addExaminationresult(TExaminationresult tExaminationresult){
		String classCode = tExaminationresult.getClasscode();
		String examCode = tExaminationresult.getExamcode();
		String studentCode = tExaminationresult.getStudentcode();
		String result = tExaminationresult.getResult();
		String sql = "insert into t_examinationresult values(null,:classcode,:examcode,:studentcode,:result)";
		return getDao().executeSQL(sql,tExaminationresult);
	}
	/**
	 * 查询考试成绩是否在数据库中存在
	 * @param tExaminationresult
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<ExamResultAll> examJudge(TExaminationresult tExaminationresult){
		String studentCode = tExaminationresult.getStudentcode();
		String examCode = tExaminationresult.getExamcode();
		String sql = "select * from t_examinationresult where studentCode = :studentcode and examcode = :examcode";
		List list = getDao().queryBySQL(sql,tExaminationresult);
		return list;
	}
	/**
	 * 修改考试
	 * @param examResultAll
	 * @return
	 */
	public int updateExam(ExamResultAll examResultAll){
		String subjectCode = examResultAll.getSubjectcode();
		Long startTime = examResultAll.getStarttime();
		Long endTime = examResultAll.getEndtime();
		Long resultTime = examResultAll.getResulttime();
		String remark = examResultAll.getRemark();
		String examCode = examResultAll.getExamcode();
		String sql = "update t_exam set subjectcode = :subjectcode, starttime = :starttime,endtime = :endtime,resulttime = :resulttime,remark = :remark "
				+ " where examcode = :examcode";
		return getDao().executeSQL(sql,examResultAll);
	}
	/**
	 * 修改考试成绩
	 * @param tExaminationresult
	 * @return
	 */
	@SuppressWarnings({"unchecked" })
	public int updateExamresult(TExaminationresult tExaminationresult){
		String result = tExaminationresult.getResult();
		String examCode = tExaminationresult.getExamcode();
		String studentCode = tExaminationresult.getStudentcode();
		
		String sql2 = "select * from t_examinationresult where examcode = :examcode and studentcode = :studentcode";
		List<TExaminationresult> lis = getDao().queryBySQL(sql2,tExaminationresult);
		if(lis.size() == 0){
			return addExaminationresult(tExaminationresult);
		}else{
			String sql = "update t_examinationresult set result = :result "
					+ " where examcode = :examcode and studentcode = :studentcode";
			return getDao().executeSQL(sql,tExaminationresult);
		}
	}
	/**
	 * 删除考试
	 * @param examResultAll
	 * @return
	 */
	public int deleteExam(ExamResultAll examResultAll){
		String examCode = examResultAll.getExamcode();
		String[] examCodeArray = examCode.split(",");
		
		String examCodeResult = "";
		for(int i=0;i<examCodeArray.length;i++){
			examCodeResult +=  "'"+examCodeArray[i]+"'"+",";
		}
		examCodeResult = examCodeResult.substring(0,examCodeResult.length()-1);
		String sql = "delete from t_exam where examcode in ("+examCodeResult+")";
		return getDao().executeSQL(sql);
	}
	
	
	public int deleteExamresult(ExamResultAll examResultAll){
		int n = 0;
		String examCode = examResultAll.getExamcode();
		String studentCode = examResultAll.getStudentcode();
		if(studentCode!=null){
			String[] studentCodeArray = studentCode.split(",");
			String studentCodeResult = "";
			for(int i=0;i<studentCodeArray.length;i++){
				studentCodeResult +=  "'"+studentCodeArray[i]+"'"+",";
			}
			studentCodeResult = studentCodeResult.substring(0,studentCodeResult.length()-1);
			String sql = "delete from t_examinationresult where examcode = :examcode and studentcode in ("+studentCodeResult+")";
			n = getDao().executeSQL(sql,examResultAll);
		}else{
			String[] examCodeArray = examCode.split(",");
			String examCodeResult = "";
			for(int i=0;i<examCodeArray.length;i++){
				examCodeResult +=  "'"+examCodeArray[i]+"'"+",";
			}
			examCodeResult = examCodeResult.substring(0,examCodeResult.length()-1);
			String sql = "delete from t_examinationresult where examcode in ("+examCodeResult+")";
			n = getDao().executeSQL(sql);
		}
		return n;
	}
	public void getUseridBy(String classcode){
		
	}

	@SuppressWarnings("unchecked")
	public ResultSetToExcel exportTable(String name) {
		String sql = "select * from "+name;
		List<Object[]> list = getDao().queryBySQL(sql);
		ResultSetToExcel rse = new ResultSetToExcel();
		rse.setColoumItem( "学生编码,学生姓名,考试成绩,年级,班级,班级编码");
		rse.setRs(list);
		rse.writeExcel();
		rse.setRs(null);
		return rse;
	}

	public boolean deleteinfo(String teachercode, String cellphone) {
		boolean flag = false;
		//将班级表的教师code设置成null
		String usql = "update t_class set teachercode = null where teachercode = '"+teachercode+"'";
		getDao().executeSQL(usql);
		//将排课表教师信息改成null
		String csql = "update t_courseorder set teachercode = null where teachercode = '"+teachercode+"'";
		getDao().executeSQL(csql);
//		//将校车表教师信息改成null
//		String bsql = "update t_bus set teachaercode = null where teachercode = '"+teachercode+"'";
//		getDao().executeSQL(bsql);
		String[] table = new String[]{"t_attendance_statistics","t_class_teacher","t_user","t_teacher","t_teacher_department"};
		String sql = "delete from ";
		for(int i=0;i<table.length;i++){
			String sql2 = sql + table[i] + " where teachercode='"+teachercode+"'";
			if(table[i]=="t_user"){
				sql2 = sql + table[i] + " where usercode='"+cellphone+"'";
			}
			table[i]=sql2;
			sql2="";
		}
		if(getDao().deleteSQL(table)>0){
			flag = true;
		}
		return flag;
	}

	/**获取老师的信息
	 * @author:wuqiwei
	 * @param @return
	 * @date:2016年1月6日上午9:38:16
	*/
	@SuppressWarnings("rawtypes")
	public List<TTeacher> queryAllTeacher(String schoolcode) {
		String hql ="SELECT tt,tu.messageid  FROM TUser tu,TTeacher tt WHERE tu.usertype='2' AND islock='1' AND tt.del='1' and tu.usercode = tt.cellphone AND tt.schoolcode = '"+schoolcode+"'";
		TTeacher tta =new TTeacher();
		List list = getDao().queryByHQL(hql,tta);
		List<TTeacher> result = new ArrayList<TTeacher>();
		for (int i = 0; i < list.size(); i++) {
			Object[] obj = (Object[]) list.get(i);
			TTeacher tt = (TTeacher) obj[0];
			tt.setMessageid(String.valueOf(obj[1]));
			result.add(tt);
		}
		return result;
	}
	
	/**根据班级号获取老师的信息
	 * @author:liwenyuan
	 * @param @return
	 * @date:2016年4月8日上午10:19:46
	*/
	@SuppressWarnings("unchecked")
	public List<TTeacher> getTeacherByClasscode(TTeacher tt) {
		List<TTeacher> listResult = new ArrayList<TTeacher>();
		String sql = "SELECT tt.*,tc.classcode FROM t_teacher tt,t_class tc WHERE 1=1 and tt.teachercode =  tc.teachercode";
		String sql2 = "SELECT teachercode FROM t_class_teacher WHERE classcode=:classcode";
		List list2 = getDao().queryBySQL(sql2, tt);
		if(tt.getClasscode()!=null && tt.getClasscode().length() > 0){
			if(list2.size()>0){
				sql += " AND tt.teachercode IN (SELECT teachercode FROM t_class_teacher WHERE classcode=:classcode)";
			}else{
				sql += " AND tt.teachercode IN (SELECT teachercode FROM t_class WHERE classcode=:classcode)";
			}
		}
		if(tt.getDel()!=null && tt.getDel().length() > 0){
			sql += " AND tt.del=:del";
		}
		if(tt.getSchoolcode() != null && tt.getSchoolcode().length() > 0){
			sql += " and tt.schoolcode = :schoolcode";
		}
		if(tt.getType() != null && tt.getType().length() > 0){
			sql += " and tc.type = :type";
		}
		if(tt.getRolecode() != null && tt.getRolecode().length() > 0){
			tt.setRolecode(tt.getRolecode().replace(",", "^"));
			sql = "select t1.* from ("+sql+")t1,(select * from t_data_role where rolecode in (:rolecode))t2 where t1.classcode = t2.classcode";
		}
		List<Object[]> list = getDao().queryBySQL(sql,tt);
		for(Object[] obj : list){
			TTeacher teacher = new TTeacher();
			teacher.setTeacherno(String.valueOf(obj[1]));
			teacher.setDepartment1(String.valueOf(obj[2]));
			teacher.setDepartment2(String.valueOf(obj[3]));
			teacher.setName(String.valueOf(obj[4]));
			teacher.setSex(String.valueOf(obj[5]));
			teacher.setNation(String.valueOf(obj[6]));
			teacher.setBirdate(Long.parseLong(String.valueOf(obj[7])));
			teacher.setBirpacle(String.valueOf(obj[8]));
			teacher.setCardid(String.valueOf(obj[9]));
			teacher.setPolitical(String.valueOf(obj[10]));
			teacher.setDegree(String.valueOf(obj[11]));
			teacher.setWork(String.valueOf(obj[12]));
			teacher.setTjob(String.valueOf(obj[13]));
			teacher.setIssueorg(String.valueOf(obj[14]));
			String workdate = String.valueOf(obj[15]);
			teacher.setWorkdate(Long.parseLong(workdate));
			teacher.setInschooldate(Long.parseLong(String.valueOf(obj[16])));
			teacher.setGraduation(String.valueOf(obj[17]));
			teacher.setRole(String.valueOf(obj[18]));
			teacher.setTeachercode(String.valueOf(obj[19]));
			teacher.setSchoolcode(String.valueOf(obj[20]));
			teacher.setPhone(String.valueOf(obj[21]));
			teacher.setCellphone(String.valueOf(obj[22]));
			teacher.setDel(String.valueOf(obj[23]));
			teacher.setRemark(String.valueOf(obj[24]));
			listResult.add(teacher);
		}
		return listResult;
	}

	public List recovery() {
		String sql ="SELECT DISTINCT usercode FROM t_user_student";
		List list = getDao().queryBySQL(sql);
		List listre = new ArrayList();
		int messageid = 10231;
		for (Object obj : list) {
			TUser tuserNew = new TUser();
			tuserNew.setUsercode(String.valueOf(obj));
			tuserNew.setUsertype(1);
			tuserNew.setHeadimg(ConfigUtil.getProperty("default","headimg"));
			tuserNew.setPassword(EncryptUtil.encryptMd5Password2(String.valueOf(obj)));
			tuserNew.setUserkey(EncryptUtil.encryptMd5Password2(tuserNew.getUsercode()+tuserNew.getUsertype()));
			tuserNew.setAlias(String.valueOf(obj));
			tuserNew.setIslock(true);
			tuserNew.setLockdate((long) 0);
			tuserNew.setLocktype(1);
			tuserNew.setCellphone(String.valueOf(obj));
			tuserNew.setSchoolcode("3446000056");
			UserService us = new UserService();
//			String mesid = us.regPushUser(tuserNew.getUsercode()+tuserNew.getUsertype(),tuserNew.getUsertype());
			messageid = messageid +1;
			tuserNew.setMessageid(String.valueOf(messageid));
			tuserNew = (TUser) getDao().create(tuserNew);	
			listre.add(tuserNew);
		}
		System.out.println(listre.size());
		return listre;
	}
}
